package net.biz.grid.gt.export;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

public class ExportXls {

    public void exportXLS(OutputStream out, List data, String[] properiesName,
			String[] headsName,String fileName) throws IOException  {
    	if(fileName.equals("reportEmpfile"))
    	  exportXLS_reportEmp(out, data, properiesName, headsName);
    	if(fileName.equals("reportRegfile"))
      	  exportXLS_reportReg(out, data, properiesName, headsName);
    	
    }
    
    //注册统计表   注册人员统计表20160418
     private void exportXLS_reportReg(OutputStream out, List data, String[] properiesName, String[] headsName) throws IOException{
  		HSSFWorkbook excelfile = new HSSFWorkbook();
  		HSSFSheet excelsheet = excelfile.createSheet();
  		 //sheet名称
  		excelfile.setSheetName(0, "注册人员统计表");
  		//合并单元格样式
  		HSSFCellStyle styleTitle = excelfile.createCellStyle();
  		HSSFFont fnt = excelfile.createFont();
  		fnt.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  		fnt.setFontHeightInPoints((short)14);
  		styleTitle.setFont(fnt);
  		//styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  		//styleTitle.setFillForegroundColor(HSSFColor.ORANGE.index);
  		styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  		styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
//       style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中  
//       style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中  
//       style.setAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//垂直底部  
//       style.setAlignment(HSSFCellStyle.VERTICAL_TOP);//垂直顶部  
  		
  		
  		//取得第一行 29.
  	     HSSFRow hssfrow=excelsheet.createRow(0); 
  	     hssfrow.createCell(0);
  	     hssfrow.setHeight((short)800);
  		//合并单元格
  		excelsheet.addMergedRegion(new CellRangeAddress(0,0,0,headsName.length-1));
  		excelsheet.getNumMergedRegions();
  		excelsheet.getRow(0).getCell(0).setCellStyle(styleTitle);
  		excelsheet.getRow(0).getCell(0).setCellValue("注册人员统计表"+new SimpleDateFormat("yyyyMMdd").format(new Date()));
  		
  		//列名单元格样式
  		HSSFCellStyle styleHead = excelfile.createCellStyle();
  		//styleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  		//styleHead.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
  		//styleHead.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  		//styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
  		HSSFFont fnt2 = excelfile.createFont();
  		fnt2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  		fnt2.setFontHeightInPoints((short)10);
  		styleHead.setFont(fnt2);
  		styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  		styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
  		styleHead.setWrapText(true);//为了能够使用换行，您需要设置单元格的样式 wrap=true 
  		
  		styleHead.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
  		styleHead.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
  		styleHead.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
  		styleHead.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
  		
  		//内容单元格样式	
  		HSSFCellStyle styleBody = excelfile.createCellStyle();
  		styleBody.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  		styleBody.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
  		styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  		styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
  		
  		styleBody.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
  		styleBody.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
  		styleBody.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
  		styleBody.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
  		

  		HSSFCellStyle styleBody2 = excelfile.createCellStyle();

  		HSSFFont fnt3 = excelfile.createFont();
  		fnt3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  		fnt3.setFontHeightInPoints((short)10);
  		styleBody2.setFont(fnt3);
  		styleBody2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  		styleBody2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直 
  		
  		styleBody2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
  		styleBody2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
  		styleBody2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
  		styleBody2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
  		
  		//表头内容
  		HSSFRow rowhead = excelsheet.createRow(1);
  		rowhead.setHeight((short)600);
  		//excelsheet.addMergedRegion(new CellRangeAddress(2,2,0,1));
  		for (int i = 0,len=headsName.length; i < len; i++) {
  			HSSFCell c = rowhead.createCell(i);
//  			if(headsName[i].length()==4){
//  				c.setCellValue(new HSSFRichTextString(headsName[i].substring(0,2)+"\n" +headsName[i].substring(2)));
//  			}else{
  				c.setCellValue(headsName[i]);
//  			}
  			c.setCellStyle(styleHead);
  		}
  		
  		
  		
 		for (int i = 0, len =data.size(); i < len; i++) {
 			HSSFRow row = excelsheet.createRow(i+2);
 			if(i==0){
 				row.setHeight((short)400);
 			}
 			Map record = (Map) data.get(i);
 			Object[] record1= map2Array(record, properiesName);
 			//xlsw.addRow(map2Array(record, properiesName));
 			for (int j = 0,l=record1.length; j < l; j++) {
 				HSSFCell c = row.createCell(j);
 				//给第三行设置颜色
 				if(i==0){
 					c.setCellStyle(styleBody);
 				}else{
 					c.setCellStyle(styleBody2);
 				}
 				
 				Object value =record1[j];
 			     if (value instanceof Number) {
 			         double dNum = Double.parseDouble(String.valueOf(value));
 			         c.setCellValue(dNum);
 			       } else {
 			         c.setCellValue(String.valueOf(value));
 			       }
 				
 			}
 		} 			
 		//调整列宽以使用内容长度  
 		excelsheet.autoSizeColumn((short)3);  
  		excelfile.write(out);
		
	}


	//员工统计表
     private void exportXLS_reportEmp(OutputStream out, List data, String[] properiesName,
			String[] headsName) throws IOException  {
 		HSSFWorkbook excelfile = new HSSFWorkbook();
 		HSSFSheet excelsheet = excelfile.createSheet();
 		 //sheet名称
 		excelfile.setSheetName(0, "员工统计表");
 		//合并单元格样式
 		HSSFCellStyle styleTitle = excelfile.createCellStyle();
 		HSSFFont fnt = excelfile.createFont();
 		fnt.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 		fnt.setFontHeightInPoints((short)14);
 		styleTitle.setFont(fnt);
 		//styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 		//styleTitle.setFillForegroundColor(HSSFColor.ORANGE.index);
 		styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 		styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
//      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中  
//      style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中  
//      style.setAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//垂直底部  
//      style.setAlignment(HSSFCellStyle.VERTICAL_TOP);//垂直顶部  
 		
 		//取得第一行 29.
 	     HSSFRow hssfrow=excelsheet.createRow(0); 
 	     hssfrow.createCell(0);
 	     hssfrow.setHeight((short)800);
 		//合并单元格
 		excelsheet.addMergedRegion(new CellRangeAddress(0,0,0,headsName.length-1));
 		excelsheet.getNumMergedRegions();
 		excelsheet.getRow(0).getCell(0).setCellStyle(styleTitle);
 		excelsheet.getRow(0).getCell(0).setCellValue("人员数量统计表");
 		
 		//列名单元格样式
 		HSSFCellStyle styleHead = excelfile.createCellStyle();
 		//styleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 		//styleHead.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
 		//styleHead.setBorderBottom(HSSFCellStyle.BORDER_THIN);
 		//styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
 		HSSFFont fnt2 = excelfile.createFont();
 		fnt2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 		fnt2.setFontHeightInPoints((short)10);
 		styleHead.setFont(fnt2);
 		styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 		styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
 		styleHead.setWrapText(true);//为了能够使用换行，您需要设置单元格的样式 wrap=true  
 		
  		styleHead.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
  		styleHead.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
  		styleHead.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
  		styleHead.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
 		
 		//内容单元格样式	
 		HSSFCellStyle styleBody = excelfile.createCellStyle();
 		styleBody.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 		styleBody.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
 		styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 		styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
  		styleBody.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
  		styleBody.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
  		styleBody.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
  		styleBody.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
  		
 		HSSFCellStyle styleBody2 = excelfile.createCellStyle();

 		HSSFFont fnt3 = excelfile.createFont();
 		fnt3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 		fnt3.setFontHeightInPoints((short)10);
 		styleBody2.setFont(fnt3);
 		styleBody2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 		styleBody2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直 		
  		styleBody2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
  		styleBody2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
  		styleBody2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
  		styleBody2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
 		
 		//表头内容
 		HSSFRow rowhead = excelsheet.createRow(1);
 		rowhead.setHeight((short)600);
 		//excelsheet.addMergedRegion(new CellRangeAddress(2,2,0,1));
 		for (int i = 0,len=headsName.length; i < len; i++) {
 			HSSFCell c = rowhead.createCell(i);
// 			if(headsName[i].length()==4){
// 				c.setCellValue(new HSSFRichTextString(headsName[i].substring(0,2)+"\n" +headsName[i].substring(2)));
// 			}else{
 				c.setCellValue(headsName[i]);
// 			}
 			c.setCellStyle(styleHead);
 		}
 		
 		
 		
		for (int i = 0, len =data.size(); i < len; i++) {
			HSSFRow row = excelsheet.createRow(i+2);
			if(i==0){
				row.setHeight((short)400);
			}
			Map record = (Map) data.get(i);
			Object[] record1= map2Array(record, properiesName);
			//xlsw.addRow(map2Array(record, properiesName));
			for (int j = 0,l=record1.length; j < l; j++) {
				HSSFCell c = row.createCell(j);
				//给第三行设置颜色
				if(i==0){
					c.setCellStyle(styleBody);
				}else{
					c.setCellStyle(styleBody2);
				}
				
				Object value =record1[j];
			     if (value instanceof Number) {
			         double dNum = Double.parseDouble(String.valueOf(value));
			         c.setCellValue(dNum);
			       } else {
			         c.setCellValue(String.valueOf(value));
			       }
				
			}
		} 			
		//调整列宽以使用内容长度  
		excelsheet.autoSizeColumn((short)3);  
 		excelfile.write(out);
	}
     
     
 	private static Object[] map2Array(Map map, String[] properiesName) {
		int len = properiesName.length;
		Object[] objs = new Object[len];
		for (int i = 0; i < len; i++) {
			objs[i] = map.get(properiesName[i]);
		}
		return objs;
	}
}
